﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using JXT.PrimaryKey.Batman.Collections;
using JXT.PrimaryKey.Batman.Domain.Specifications;
using JXT.PrimaryKey.Batman.Domain.Repositories.MySql;
using JXT.PrimaryKey.Batman.Domain.Quotation.Models;
using JXT.PrimaryKey.Batman.Domain.Quotation.Repositories;
using JXT.PrimaryKey.Batman.Domain.Repositories.SqlServer.Expressions;

namespace JXT.PrimaryKey.Batman.Domain.Repositories.Quotation
{
    public class MySqlMarketStatisticRepository : MySqlRepositoryBase<MarketStatistic, int>, IMarketStatisticRepository
    {
        public MySqlMarketStatisticRepository(RepositoryContext context)
            : base((MySqlRepositoryContext)context)
        { }

        public WeekMarketStatistic GetWeekSingle(ISpecification<WeekMarketStatistic> specification)
        {
            var expressionSpecifcation = specification.ToExpressionSpecification();
            IDataParameter[] parameters = null;
            string sql = expressionSpecifcation.BuildSingleQueryString(out parameters);
            WeekMarketStatistic result = null; //必须将初始值设置为null
            using (var dr = Context.ExecuteDataReader(sql, parameters))
            {
                if (dr.Read())
                {
                    result = ReadFromDataReader<WeekMarketStatistic>(dr);
                }
            }
            return result;
        }

        public IPagedCollection<WeekMarketStatistic> GetWeekList(IPagedSpecification<WeekMarketStatistic> specification)
        {
            var expressionSpecifcation = specification.ToExpressionSpecification();
            IDataParameter[] parameters = null;
            string sql = expressionSpecifcation.BuildQueryString(out parameters);
            IList<WeekMarketStatistic> list = new List<WeekMarketStatistic>();
            using (var dr = Context.ExecuteDataReader(sql, parameters))
            {
                while (dr.Read())
                {
                    var item = ReadFromDataReader<WeekMarketStatistic>(dr);
                    list.Add(item);
                }
            }
            return new PagedCollection<WeekMarketStatistic>(0, specification.PageIndex, specification.PageSize, list);
        }

        public MonthMarketStatistic GetMonthSingle(ISpecification<MonthMarketStatistic> specification)
        {
            var expressionSpecifcation = specification.ToExpressionSpecification();
            IDataParameter[] parameters = null;
            string sql = expressionSpecifcation.BuildSingleQueryString(out parameters);
            MonthMarketStatistic result = null; //必须将初始值设置为null
            using (var dr = Context.ExecuteDataReader(sql, parameters))
            {
                if (dr.Read())
                {
                    result = ReadFromDataReader<MonthMarketStatistic>(dr);
                }
            }
            return result;
        }

        public IPagedCollection<MonthMarketStatistic> GetMonthList(IPagedSpecification<MonthMarketStatistic> specification)
        {
            var expressionSpecifcation = specification.ToExpressionSpecification();
            IDataParameter[] parameters = null;
            string sql = expressionSpecifcation.BuildQueryString(out parameters);
            IList<MonthMarketStatistic> list = new List<MonthMarketStatistic>();
            using (var dr = Context.ExecuteDataReader(sql, parameters))
            {
                while (dr.Read())
                {
                    var item = ReadFromDataReader<MonthMarketStatistic>(dr);
                    list.Add(item);
                }
            }
            return new PagedCollection<MonthMarketStatistic>(0, specification.PageIndex, specification.PageSize, list);
        }

    
        public WeekMarketStatistic GetLastWeekDate(string code,MarketType markettype, DateTime dt)
        {
            string sql = "select * from `weekmarketstatistic` where `code`=@code and `updatetime`<@dt and `markettype`=@markettype order by `updatetime` desc limit 1";
            IDataParameter[] parameters=new[]
            {
                Context.CreateParameter("@code",code),
                Context.CreateParameter("@dt",dt),
                Context.CreateParameter("@markettype",markettype)
            };
            WeekMarketStatistic item=null;
            using (var dr = Context.ExecuteDataReader(sql, parameters))
            {
                if (dr.Read())
                {
                    item = ReadFromDataReader<WeekMarketStatistic>(dr);
                }
            }
            return item;
        }

        /// <summary>
        /// 上一期的月度数据
        /// </summary>
        /// <param name="code"></param>
        /// <param name="markettype"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public MonthMarketStatistic GetLastMonthDate(string code, MarketType markettype, DateTime dt)
        {
            string sql = "select * from `monthmarketstatistic` where `code`=@code and `updatetime`<@dt and `markettype`=@markettype order by `updatetime` desc limit 1";
            IDataParameter[] parameters = new[]
            {
                Context.CreateParameter("@code",code),
                Context.CreateParameter("@dt",dt),
                Context.CreateParameter("@markettype",markettype)
            };
            MonthMarketStatistic item = null;
            using (var dr = Context.ExecuteDataReader(sql, parameters))
            {
                if (dr.Read())
                {
                    item = ReadFromDataReader<MonthMarketStatistic>(dr);
                }
            }
            return item;
        }

        public MarketStatistic GetLastDayData(string code, MarketType markettype, DateTime dt)
        {
            string sql = "select * from `marketstatistic` where `code`=@code and `updatetime`<@dt and `markettype`=@markettype order by `updatetime` desc limit 1";
            IDataParameter[] parameters = new[]
            {
                Context.CreateParameter("@code",code),
                Context.CreateParameter("@dt",dt),
                Context.CreateParameter("@markettype",markettype)
            };
            MarketStatistic item = null;
            using (var dr = Context.ExecuteDataReader(sql, parameters))
            {
                if (dr.Read())
                {
                    item = ReadFromDataReader<MarketStatistic>(dr);
                }
            }
            return item;
        }

        public IEnumerable<WeekMarketStatistic> GetWeekList(string code, MarketType markettype, Tuple<DateTime, DateTime> tuple)
        {
            string sql = "select * from `marketstatistic` where code=@code and `markettype` =@markettype and  `updatetime`  >= @starttime and `updatetime` <= @endtime order by `updatetime` asc";
            IDataParameter[] parameters = new[]
            {
                Context.CreateParameter("@code",code),
                Context.CreateParameter("@markettype",markettype),
                Context.CreateParameter("@starttime",new global::MySql.Data.Types.MySqlDateTime(tuple.Item1.Date)),
                Context.CreateParameter("@endtime",new global::MySql.Data.Types.MySqlDateTime(tuple.Item2.Date))
            };
            
            IList<WeekMarketStatistic> list=new List<WeekMarketStatistic>();
            using (var dr = Context.ExecuteDataReader(sql, parameters))
            {
                while (dr.Read())
                {
                    list.Add(ReadFromDataReader<WeekMarketStatistic>(dr));
                }
            }
            return list;

        }

        public WeekMarketStatistic GetWeekSingle(string code, MarketType markettype, DateTime dt)
        {
            string sql = "SELECT * FROM `weekmarketstatistic` where `code`=@code and `markettype`=@markettype and `updatetime`=@dt";
            IDataParameter[] parameters = new[]
            {
                Context.CreateParameter("@code",code),
                Context.CreateParameter("@markettype",markettype),
                Context.CreateParameter("@dt",new global::MySql.Data.Types.MySqlDateTime(dt.Date)),
                
            };
            WeekMarketStatistic item = null;
            using (var dr = Context.ExecuteDataReader(sql, parameters))
            {
                if (dr.Read())
                {
                    item=ReadFromDataReader<WeekMarketStatistic>(dr);
                }
            }
            return item;
        }

        public IEnumerable<MonthMarketStatistic> GetMonthList(string code,MarketType markettype,DateTime starttime,DateTime endtime)
        {
            string sql = "select * from `marketstatistic` where code=@code and `markettype` =@markettype and  `updatetime`  >= @starttime and `updatetime` <= @endtime order by `updatetime` asc";
            IDataParameter[] parameters = new[]
            {
                Context.CreateParameter("@code",code),
                Context.CreateParameter("@markettype",markettype),
                Context.CreateParameter("@starttime",new global::MySql.Data.Types.MySqlDateTime(starttime)),
                Context.CreateParameter("@endtime",new global::MySql.Data.Types.MySqlDateTime(endtime))
            };

            IList<MonthMarketStatistic> list = new List<MonthMarketStatistic>();
            using (var dr = Context.ExecuteDataReader(sql, parameters))
            {
                while (dr.Read())
                {
                    list.Add(ReadFromDataReader<MonthMarketStatistic>(dr));
                }
            }
            return list;
        }

        /// <summary>
        /// 查询当期的月度数据
        /// </summary>
        /// <param name="code"></param>
        /// <param name="markettype"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public MonthMarketStatistic GetMonthSingle(string code,MarketType markettype,DateTime dt)
        {
            string sql = "SELECT * FROM `monthmarketstatistic` where `code`=@code and `markettype`=@markettype and `updatetime`=@dt";
            IDataParameter[] parameters = new[]
            {
                Context.CreateParameter("@code",code),
                Context.CreateParameter("@markettype",markettype),
                Context.CreateParameter("@dt",new global::MySql.Data.Types.MySqlDateTime(dt.Date)),
                
            };
            MonthMarketStatistic item = null;
            using (var dr = Context.ExecuteDataReader(sql, parameters))
            {
                if (dr.Read())
                {
                    item = ReadFromDataReader<MonthMarketStatistic>(dr);
                }
            }
            return item;
        }


        public void DeleteDayData(MarketType markettype, DateTime dt)
        {
            string sql = "delete from `marketstatistic` where `markettype`=@markettype and `updatetime`=@dt";
            var parameters = new[]
            {
                Context.CreateParameter("@markettype",markettype),
                Context.CreateParameter("@dt",dt)
            };
            Context.ExecuteNonQuery(sql, parameters);
        }
    }
}
